

// Based on Fig. 18.24: TableDisplay.java in Deitel & Deitel "Java How To
// Program", Third Edition, Prentice Hall 1999
// This program displays the contents of the Student table in the
// 675Examples Database
import java.sql.*;


import java.util.*;
import java.io.*;
import org.jdom.Attribute;

//import org.jdom.input.SAXBuilder;
import org.jdom.Document;
//import org.jdom.JDOMException;
import org.jdom.Element;
//import org.jdom.CDATA;
import org.jdom.Comment;
//import org.jdom.output.XMLOutputter;
//import org.jdom.output.Format;

// DOM
public class DBConnectAndRetrieve {  //CHANGE THIS!!

    private Connection connection;

    public DBConnectAndRetrieve() //CHANGE THIS!!
    {
        // The URL specifying the 675Examples database to which
        // this program connects using JDBC to connect to a
        // Microsoft ODBC database.
        /*String url = "jdbc:odbc:SQLEXPRESS2008EXAMPLES";  //CHANGE THIS!
        String username = "sa";
        String password = "saradhi1";

        // Load the driver to allow connection to the database
        try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        connection = DriverManager.getConnection(
        url, username, password);
        } catch (ClassNotFoundException cnfex) {
        System.err.println(
        "Failed to load JDBC/ODBC driver.");
        cnfex.printStackTrace();
        System.exit(1);  // terminate program
        } catch (SQLException sqlex) {

        System.err.println("Unable to connect");
        sqlex.printStackTrace();
        }

        getTable();*/
        startDB();

    }

    private void startDB() {

        // The URL specifying the 675Examples database to which
        // this program connects using JDBC to connect to a
        // Microsoft ODBC database.
        String url = "jdbc:odbc:SQLEXPRESS2008EXAMPLES";  //CHANGE THIS!
        String username = "sa";
        String password = "saradhi1";

        // Load the driver to allow connection to the database
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            connection = DriverManager.getConnection(
                    url, username, password);
        } catch (ClassNotFoundException cnfex) {
            System.err.println(
                    "Failed to load JDBC/ODBC driver.");
            cnfex.printStackTrace();
            System.exit(1);  // terminate program
        } catch (SQLException sqlex) {

            System.err.println("Unable to connect");
            sqlex.printStackTrace();
        }


        // return doc;

    }

    public Document getTable(String lat,String longi, String interest,String time) {
        Statement statement;
        ResultSet resultSet;

        try {
           /* String query = "SELECT * FROM master.dbo.Intrest_Table i, master.dbo.Sample_Venue_Address s WHERE latitude = " + lat
                    + " AND logitude =" + longi
                    + " AND i.venue_id= s.venue_id"
                    + " AND intrest = '" + interest + "' AND time >" + time + ";"; */
             String query = "SELECT * FROM master.dbo.Intrest_Table i, master.dbo.Sample_Venue_Address s WHERE "
                    + " i.venue_id= s.venue_id"
                    + " AND intrest = '" + interest + "' AND time >" + time + ";";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(query);
            Document resDoc = displayResultSet(resultSet);
            statement.close();
            return resDoc;
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            return null;
        }
    }

    private Document displayResultSet(ResultSet rs)
            throws SQLException {
        // position to first record
        boolean moreRecords = rs.next();

        // If there are no records, display a message
        if (!moreRecords) {
            System.out.println("ResultSet contained no records");
            return null;
        }
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            return getXmlDocument(rs, rsmd);
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            return null;
        }
    }

    private Document getXmlDocument(ResultSet rs,
            ResultSetMetaData rsmd)
            throws SQLException {
        // Create the root element
        Element optionDetElement = new Element("all_option_details");
        //create the document
        Document doc = new Document(optionDetElement);
        //add a comment
        optionDetElement.addContent(new Comment("Complete Option Details"));
        int index=1;
        do {
            Element subOptionElement = new Element("option_detail_"+index);
            optionDetElement.addContent(subOptionElement);
            for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
                if (rsmd.getColumnLabel(i).equals("address")) {
                    subOptionElement.addContent(new Element("address").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("city")) {
                    subOptionElement.addContent(new Element("city").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("state")) {
                    subOptionElement.addContent(new Element("state").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("country")) {
                    subOptionElement.addContent(new Element("country").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("zip")) {
                    subOptionElement.addContent(new Element("zip").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("phone")) {
                    subOptionElement.addContent(new Element("phone").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("latitude")) {
                    subOptionElement.addContent(new Element("latitude").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("logitude")) {
                    subOptionElement.addContent(new Element("logitude").addContent(rs.getString(i)));
                } else if (rsmd.getColumnLabel(i).equals("description")) {
                    subOptionElement.addContent(new Element("description").addContent(rs.getString(i)));
                }
            }
            index++;
        } while (rs.next());
        return doc;
    }

    public void shutDown() {
        try {
            connection.close();
        } catch (SQLException sqlex) {
            System.err.println("Unable to disconnect");
            sqlex.printStackTrace();
        }
    }

    /* public static void main(String args[]) {
    final DBConnectAndRetrieve app = new DBConnectAndRetrieve();  //CHANGE THIS!
    }*/
}
